﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using Models;
using Aop.Api.Response;



namespace DAL
{
    public class MenuReservationDAL
    {
        //public string conn = ConfigurationSettings.AppSettings["con"].ToString();


#pragma warning disable CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”
        public static string conn = ConfigurationSettings.AppSettings["con"].ToString();
#pragma warning restore CS0618 // “ConfigurationSettings.AppSettings”已过时:“This method is obsolete, it has been replaced by System.Configuration!System.Configuration.ConfigurationManager.AppSettings”

        //获取订餐菜单

        public int Addtxt( string text)
        {

            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@text",text)
              
            };
            string sqlstr = "insert into dbo.tcp(text) values(@text)";
            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
        }
        public DataTable getall()
        {
            string sqlstr = "select * from  MenuType";

            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr).Tables[0];
        }



        public int getCState(string OrderId)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter ("@OrderId",OrderId)
            };

            string sqlstr = "update orderlist set state='3' where orderid =@OrderId;update OrderDetail set istake = 'True' where orderid = @OrderId";

            //string sqlstr1 = "update orderlist set state='3' where orderid =@OrderId";
            //string sqlstr2 = "update OrderDetail set istake = 'True' where orderid = @OrderId";
            //string sqlstr = sqlstr1 + sqlstr2;                
          
            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
        }

     








        public DataTable getcbReservation()
        {
            string sqlstr = "select * from  cbReservation";

            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr).Tables[0];
        }
        public DataTable Notorder()
        {
            string sqlstr = "select * from  Notorder";

            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr).Tables[0];
        }
        public DataSet getTimeState(string sdmc,string openid,string canteenss)
        {
            SqlParameter[] sp = new SqlParameter[] {
                  new SqlParameter("@sdmc",sdmc),
                  new SqlParameter("@openid",openid),
                  new SqlParameter("@canteenss",canteenss)
            };
            string sqlstr = "";
            if (canteenss == "超市")
            {
                sqlstr = "select * from dbo.TimeState where sdmc=@sdmc        select top(1) * from xfsd where klb=( select klb from ryxx where ryid=(select ryid from idbind where openid=@openid) ) and jh in (select jh From jhsz where wz=@canteenss)    ";
            }
            else
            {
              //  sqlstr = "select * from dbo.TimeState where sdmc=@sdmc        select top(1) * from xfsd where klb=( select klb from ryxx where ryid=(select ryid from idbind where openid=@openid) )  and jh=0     ";

                sqlstr = "select * from dbo.TimeState where sdmc=@sdmc    ";
            }

            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr,sp) ;
        }

        //按上架id查询菜品剩余份数
        public DataTable MeunumberCount(string onsaleid)
        {
            DataTable dataTable = new DataTable();
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@onsaleid",onsaleid)
            };
            try
            {
                string sql = "select ISNULL(a.Inventories,0) as MeuNumber from MenuList a inner join Shelves b on a.MenuId =b.MenuId where b.OnSaleId =@onsaleid";
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("MeunumberCount错误：" + ex.Message);
                return dataTable;
            }
        }

        //按上架id查询菜品剩余份数
        public DataTable MeunumberCount01(string onsaleid)
        {
            DataTable dataTable = new DataTable();
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@onsaleid",onsaleid)
            };
            try
            {
                string sql = "select ISNULL(b.ShelvesNum,0) as MeuNumber from MenuList a inner join Shelves b on a.MenuId =b.MenuId where b.OnSaleId =@onsaleid";
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("MeunumberCount错误：" + ex.Message);
                return dataTable;
            }
        }

        public string getcount(string seldate_dummy, string canteen, string TimeStateId, string openid)
        {
            SqlParameter[] sp = new SqlParameter[] {
                  new SqlParameter("@seldate_dummy",seldate_dummy),
                  new SqlParameter("@canteen",canteen),
                  new SqlParameter("@TimeStateId",TimeStateId),
                  new SqlParameter("@openid",openid)
            };
            string sql = @"select isnull(Sum(OrderNum),0)  as OrderNum from OrderList o inner join OrderDetail l on 
o.OrderId=l.OrderId inner join Shelves s on l.OnSaleId=s.OnSaleId 
where 1=1 and reservationdate=@seldate_dummy and canteen=@canteen and TimeStateId=@TimeStateId
and Ryid in (select Ryid from IdBind where openid=@openid) and State in('2','3')";

            return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, sp).ToString();
        }

        public string getcount01(string seldate_dummy, string canteen, string TimeStateId, string openid)
        {
            SqlParameter[] sp = new SqlParameter[] {
                  new SqlParameter("@seldate_dummy",seldate_dummy),
                  new SqlParameter("@canteen",canteen),
                  new SqlParameter("@TimeStateId",TimeStateId),
                  new SqlParameter("@openid",openid)
            };
            string sql = @"select isnull(Sum(OrderNum),0)  as OrderNum from OrderList o inner join OrderDetail l on 
o.OrderId=l.OrderId inner join Shelves s on l.OnSaleId=s.OnSaleId 
where 1=1 and reservationdate=@seldate_dummy and canteen=@canteen and TimeStateId in (select TimeStateId From Shelves where OnSaleId=@TimeStateId)
and Ryid in (select Ryid from IdBind where openid=@openid) and State in('2','3')";

            return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, sp).ToString();
        }

        /// <summary>
        /// 获取订单
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public DataTable MeuListOrder(string order)
        {
            DataTable dataTable = new DataTable();
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@order",order)
            };
            try
            {
                string sql = "select a.*,b.OnSaleId,b.ShelvesNum,m.* from OrderDetail a inner join Shelves b on a.OnSaleId =b.OnSaleId inner join MenuList m on b.MenuId=m.MenuId  where OrderId =@order";
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("MeuListOrder错误：" + ex.Message);
                return dataTable;
            }
        }



        //获取到订餐编号
        public string GetOrderlist_Count(string Orderid, string canteen,string Time)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Orderid",Orderid),
                new SqlParameter("@canteen",canteen),
                new SqlParameter("@Time",Time)
            };
            string sql = string.Empty;
            string Times = "18:30:00";
            DateTime dt1 = Convert.ToDateTime(Time);
            DateTime dt2 = Convert.ToDateTime(Times);

            if (dt2 > dt1)
            {
                sql = @"select right(REPLICATE('0',10)+CONVERT( varchar(10), COUNT(distinct o.OrderId)),4)  as counts from OrderList o 
                inner join OrderDetail l on o.OrderId = l.OrderId where canteen = @canteen and o.State in ('2', '3', '4')
                and PayTime between CONVERT(datetime, CONVERT(nvarchar(10),DATEADD(day, -1, GETDATE()),121)+' 19:00:00',120) and CONVERT(datetime, CONVERT(nvarchar(10),DATEADD(day, 0, GETDATE()),121)+' 18:59:59',120)
                ";
            }
            else
            {
                sql = @"select right(REPLICATE('0',10)+CONVERT( varchar(10), COUNT(distinct o.OrderId)),4)  as counts from OrderList o 
                inner join OrderDetail l on o.OrderId = l.OrderId where canteen = @canteen and o.State in ('2', '3', '4')
                and PayTime between CONVERT(datetime, CONVERT(nvarchar(10),DATEADD(day, 0, GETDATE()),121)+' 19:00:00',120) and CONVERT(datetime, CONVERT(nvarchar(10),DATEADD(day, 0, GETDATE()),121)+' 23:59:59',120)
                ";
            }
            try
            {
                return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, sp).ToString();
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("GetOrderlist_Count错误：" + ex.Message);
                throw;
            }
        }

        //修改订单号给订单号添加取餐号
        public string Update_Orderlist(string Orderid, string meals_Orderid)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Orderid",Orderid),
                new SqlParameter("@meals_Orderid",meals_Orderid)
            };
            string sql = @"update OrderList set OrderId=@meals_Orderid where OrderId=@Orderid
                           update OrderDetail set OrderId=@meals_Orderid where OrderId=@Orderid   
                           update lssj set OrderId=@meals_Orderid where OrderId=@Orderid 
                        ";
            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp).ToString();
        }




        public DataTable getMenuList(string TimeStateId ,string rq ,string canteen, string FoodCategoryId,string Trade_name)
        {
            SqlParameter[] sp = new SqlParameter[] {
                  new SqlParameter("@TimeStateId",TimeStateId),
                  new SqlParameter("@canteen",canteen),
                  new SqlParameter("@rq",rq),
                  new SqlParameter("@FoodCategoryId",FoodCategoryId),
                  new SqlParameter("@Trade_name","%"+Trade_name+"%")
            };
            //string sqlstr = @"select * from MenuList m,Shelves s,dbo.TimeState t,canteen cc where m.MenuId=s.MenuId and s.TimeStateId=t.sdid and s.canteen_id=cc.canteen_code";
            string sqlstr = "";

            if (canteen != "超市")
            {
                sqlstr = @"select * from MenuList m,Shelves s,dbo.TimeState t,canteen cc where m.MenuId=s.MenuId and s.TimeStateId=t.sdid and s.canteen_id=cc.canteen_code  and mark='0' ";

                if (TimeStateId != "")
                {
                    sqlstr = sqlstr + " and s.TimeStateId=@TimeStateId";
                }
                if (rq != "")
                {
                    sqlstr = sqlstr + " and s.onshelvesrq=@rq";
                }

            }
            else 
            {
                sqlstr = @"select * from MenuList m,Shelves s,dbo.TimeState t,canteen cc where m.MenuId=s.MenuId and s.TimeStateId=t.sdid and s.canteen_id=cc.canteen_code  and mark='0'  and Inventories>0  and s.TimeStateId='4' and canteen_id='2'   ";
                //sqlstr = @"select distinct m.MenuId,m.Image,m.Name,m.Price,s.TimeStateId,m.onsaleid as OnSaleId1,m.Inventories,t.sdmc,Top_display from MenuList m,Shelves s,dbo.TimeState t,canteen cc where m.MenuId=s.MenuId and s.TimeStateId=t.sdid and s.canteen_id=cc.canteen_code  and mark='0'  and Inventories>0  and s.TimeStateId='4' ";
            }
            if (canteen != "")
            {
                sqlstr = sqlstr + " and cc.canteen_name=@canteen";
            }
            if (Trade_name != "")
            {
                sqlstr = sqlstr + " and [name] like @Trade_name";
            }
            if (FoodCategoryId != "" && FoodCategoryId != null && FoodCategoryId != "0")
            {
                sqlstr = sqlstr + " and FoodCategoryId=@FoodCategoryId";
            }
            sqlstr = sqlstr + "  order by Top_display asc";
            try
            {
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];
            }
            catch (Exception ex)
            {

                ApplicationLog.Write("获取菜单列表异常信息为："+ex.Message);
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];
            }
            
        }




        public DataSet getCanteen(string openid_canteen)
        {
            SqlParameter[] sp = new SqlParameter[] {
                  new SqlParameter("@openid",openid_canteen),
            };
            string sqlstr = @"select * from  canteen  select count(*) qx from xfsd where klb=(select klb from ryxx where ryid=(select ryid from idbind where openid=@openid  )) and jh=0  ";
         
            try
            {
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp);
            }
            catch (Exception ex)
            {

                ApplicationLog.Write("获取食堂列表异常信息为：" + ex.Message);
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp);
            }

        }


        //获取到菜品分类
        public DataSet getFoodCategoryTwo(string openid_canteen)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@canteen",openid_canteen)
            };
            //string sqlstr = @"select *From FoodCategoryTwo where 1=1  order by FoodCategoryId asc  ";
            string sqlstr = @"select distinct a.FoodCategoryId,a.Name From FoodCategoryTwo a inner join MenuList b on a.FoodCategoryId=b.FoodCategoryId 
inner join Shelves c on b.MenuId=c.MenuId where 1=1 and c.canteen_id in (select canteen_code From canteen where canteen_name=@canteen) order by FoodCategoryId asc  ";



            try
            {
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp);
            }
            catch (Exception ex)
            {

                ApplicationLog.Write("获取菜品分类列表异常信息为：" + ex.Message);
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp);
            }

        }





        //获取订单列表
        public DataTable  getOrderList(string State,string ryids,string types)
        {
            
              SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@State",State),
                 new SqlParameter("@ryid",ryids),
                   new SqlParameter("@types",types)

            };

           return  SqlHelper.ExecuteDataSet(conn, CommandType.StoredProcedure, "orderlists", sp).Tables[0];
        }
        public DataTable getMenuName(string name)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@name","%" + name + "%")

            };
            string sqlstr = "select Name from dbo.MenuList where Name like @name";
         
            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr,sp).Tables[0];
        }
        //根据菜单名称查询时间段
        public DataTable getStateID(string name)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@name",name)

            };
            string sqlstr = "select TimeStateId from dbo.Shelves where OnSaleId=(select MenuId from dbo.MenuList where Name=@name)";

            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];
        }
      /// <summary>
      /// 把订单详细添加到详细表
      /// </summary>
      /// <param name="od"></param>
      /// <returns></returns>
        public int AddOrderDetail(OrderDetail od)
        {
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@OrderId",od.OrderId),
                new SqlParameter("@OnSaleId",od.OnSaleId),
                new SqlParameter("@OrderNum",od.OrderNum),
                new SqlParameter("@reservationdate",od.reservationdate),
                new SqlParameter("@canteen",od.canteen)
            };
                string sqlstr = @"insert into dbo.OrderDetail(OrderId,OnSaleId,OrderNum,reservationdate,canteen) values
                                                                                        (@OrderId,@OnSaleId,@OrderNum,@reservationdate,@canteen)";
                return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("添加订单号失败AddOrderDetail：" + ex.Message);
                return -1;
            }
         

        }
       /// <summary>
       /// 把订单添加到订单表中
       /// </summary>
       /// <param name="ol"></param>
       /// <returns></returns>
        public int AddOrderList(OrderList ol)
        {
            try
            {

            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@OrderId",ol.OrderId),
                new SqlParameter("@Ryid",ol.Ryid),
                new SqlParameter("@TotalPrice",ol.TotalPrice),
                new SqlParameter("@State",ol.State),
                new SqlParameter("@OrderTime",ol.OrderTime),
               // new SqlParameter("@PayTime",ol.PayTime),
                new SqlParameter("@payType",ol.payType)
                      
            };
            
            string sqlstr = "insert into dbo.OrderList(OrderId,Ryid,TotalPrice,State,OrderTime,payType) values(@OrderId, @Ryid, @TotalPrice, @State, @OrderTime, @payType)";
            return SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("添加订单AddOrderList错误：" + ex.Message);
                return -1;
            }

        }
        /// <summary>
        /// 通过openid得到人员id
        /// </summary>
        /// <param name="openid"></param>
        /// <returns></returns>
        public DataTable getryid(string openid)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@openid",openid)

            };
            string sqlstr = "select ryid from dbo.IdBind where openid=@openid";

            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];
        }
        /// <summary>
        /// 得到卡内余额
        /// </summary>
        /// <param name="oederIds">订单号</param>
        /// <returns></returns>
        public DataTable getryye(string oederIds)
        {
            SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@OrderId",oederIds)

            };
            string sqlstr = "select rfye from ryxx where ryid=(select ryid from dbo.OrderList where OrderId=@OrderId)";

            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];
        }
        public int updateOrderList(string PayTime,string payType,string orderid, string address,string address2, string telnum)
        {
            SqlParameter[] sp = new SqlParameter[]{
                 new SqlParameter("@PayTime",PayTime),
                 new SqlParameter("@payType",payType),
                 new SqlParameter("@orderid",orderid),
                 new SqlParameter("@address",address),
                 new SqlParameter("@address2",address2),
                 new SqlParameter("@telnum",telnum)
            };
            
            string sqlstr = "update dbo.OrderList set State=2,PayTime=@PayTime,payType=@payType,address=@address,address1=@address2,telnum=@telnum,address4='1' where OrderId=@orderid";
            int i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            return i;
        }

        //消费成功后扣除相对应数量
        public int updateMenulist(int Num, string MenuId, string OnSaleIdm, string canteen)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Num",Num),
                new SqlParameter("@MenuId",MenuId),
                new SqlParameter("@OnSaleIdm",OnSaleIdm),
                new SqlParameter("@canteen",canteen)
            };
            string sqlstr = "";
            if (canteen == "超市")
            {
                sqlstr = "update MenuList set Inventories=Inventories-@Num where MenuId=@MenuId";
            }
            else
            {
                sqlstr = "update Shelves set ShelvesNum=ShelvesNum-@Num where OnSaleId=@OnSaleIdm";
            }
            int i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            return i;
        }


        //微信插入电话和地址
        public int updateOrderList2(string PayTime, string payType, string orderid, string address,string address2, string telnum)
        {
            SqlParameter[] sp = new SqlParameter[]{
                 new SqlParameter("@PayTime",PayTime),
                 new SqlParameter("@payType",payType),
                 new SqlParameter("@orderid",orderid),
                 new SqlParameter("@address",address),
                 new SqlParameter("@address2",address2),
                 new SqlParameter("@telnum",telnum)
            };

            string sqlstr = "update dbo.OrderList set address=@address,address1=@address2,telnum=@telnum where OrderId=@orderid";
            int i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            return i;
        }


        public int updateOrderList3(string PayTime, string payType, string orderid, string address, string telnum)
        {
            SqlParameter[] sp = new SqlParameter[]{
                 new SqlParameter("@PayTime",PayTime),
                 new SqlParameter("@payType",payType),
                 new SqlParameter("@orderid",orderid),
                 new SqlParameter("@address",address),
                 new SqlParameter("@telnum",telnum)
            };

            string sqlstr = "update dbo.OrderList set State=2,PayTime=@PayTime,payType=@payType,address4='1' where OrderId=@orderid";
            int i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            return i;
        }






        //------更新用户金额------
        public static int updateryxx(string orderid, string price,string jfzt)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@OrderId",orderid),
                new SqlParameter("@price",double.Parse(price)),
                new SqlParameter("@jfzt",jfzt)
            };
            try
            {
                // string jfzt1 = jfzt;
                if (jfzt == "true")
                {

                    string i = SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "XFPayAdd", sp).ToString();
                    return Int32.Parse(i);

                }
                else
                {
                    string i = SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "XFPayAdd", sp).ToString();
                    return Int32.Parse(i);
                }
            }
#pragma warning disable CS0168 // 声明了变量“ex”，但从未使用过
            catch (Exception ex)
#pragma warning restore CS0168 // 声明了变量“ex”，但从未使用过
            {

                return -1;
            }



        }



        /// <summary>
        /// 通过编号查询余额
        /// </summary>
        /// <param name="user_phone"></param>
        /// <returns></returns>
        public static DataTable Select_CUST_ID(string rfkh)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@rfkh",rfkh)
            };
                string sql = "select bankcard,dz,bz,rfkh,rybh From ryxx r inner join OrderList o on r.ryid=o.Ryid where 1=1 and rfzt='32' and OrderId=@rfkh";
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错方法Select_CUST_ID：" + ex.Message);
                return dataTable;
            }
        }



        /// <summary>
        /// 通过机号查询商家编号、柜台编号
        /// </summary>
        /// <param name="user_phone"></param>
        /// <returns></returns>
        public static DataTable Get_BUSINESS_ID(string jh)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@jh",jh)
            };
                string sql = "select MacAddress,xlmc From jhsz where jh=@jh";
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错方法Get_BUSINESS_ID：" + ex.Message);
                return dataTable;
            }
        }




        public DataTable getpayType(string oederid)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@orderid",oederid)
                
            };
            string sqlstr = "select payType from dbo.OrderList where OrderId=@orderid";
            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sqlstr, sp).Tables[0];  
        }

        public int reservation(string oederIds, string price, string payTime)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@orderid",oederIds),
                new SqlParameter("@price",price),
                new SqlParameter("@xfsj",payTime)   
            };
            string i = SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "reservation", sp).ToString();
            return Int32.Parse(i);
        }
        //退餐删除订单
        public int Refund(string OR)
        {
            SqlParameter[] sp = new SqlParameter[]{
                new SqlParameter("@oederid",OR)
            };
            string sqlstr = @"update  OrderList set State=4 where OrderId=@oederid  
                                        declare @zt char(20)	
                                            declare @zt1 char(20)
                                            declare @num int	
                                            declare @i int	
                                            declare @fzt varchar(5)
                                            set @fzt='02'
                                            set @zt=	(select top(1)zt from lssj where orderid=@oederid order by cjsj asc)	
                                            set @zt1=	(select top(1)zt from lssj where orderid=@oederid order by cjsj desc)
                                            set @num=	(select count(*) from lssj where orderid=@oederid )
                                            set @i=0	
                                            while @i<@num 	
                                                begin		
                                                    if((@zt1='00' or @zt='00') and @i=0)			
                                                        begin		
                                                            update dbo.ryxx set rfye = rfye+(select xfje from dbo.lssj where OrderId=@oederid and zt='00'),
                                                            grye =grye+(select xfje from dbo.lssj where OrderId=@oederid and zt='00') where 
                                                            ryid=(select ryid from dbo.OrderList where OrderId=@oederid)  
                                                            insert into lssj(ryid,rfkh,xfjh,xfje,rfye,xfsj,xffs,xfzl,sky,bs,zt,cdmc,grje,btje,cb,cjsj,OrderId,canteen) 
                                                            select ryid,rfkh,(select top(1) xfjh From lssj where OrderId=@oederid),-(select xfje from dbo.lssj where OrderId=@oederid and zt='00'),
                                                            (select rfye from ryxx where ryid=(select ryid from dbo.OrderList where OrderId=@oederid )),
                                                            getdate(),'正常消费','消费','管理员',null,'00','',(select grye from ryxx where ryid=(select ryid from dbo.OrderList where OrderId=@oederid )),
                                                            (select btye from ryxx where ryid=(select ryid from dbo.OrderList where OrderId=@oederid )),
                                                            null,getdate(),@oederid,(select top(1) canteen From lssj where OrderId=@oederid) from lssj where orderid=@oederid and zt='00'   	
                                                        end		
                                                 else			
                                                        begin				
                                                   
                                                            update dbo.ryxx set rfye = rfye+isnull((select xfje from dbo.lssj where OrderId=@oederid and zt='02'),0),
                                                            btye =btye+isnull((select xfje from dbo.lssj where OrderId=@oederid and zt='02'),0) where 
                                                            ryid=(select ryid from dbo.OrderList where OrderId=@oederid)		
                                                            insert into lssj(ryid,rfkh,xfjh,xfje,rfye,xfsj,xffs,xfzl,sky,bs,zt,cdmc,grje,btje,cb,cjsj,OrderId,canteen) 
                                                            select ryid,rfkh,(select top(1) xfjh From lssj where OrderId=@oederid), -(select xfje from dbo.lssj where OrderId=@oederid and zt='02'),
                                                            (select rfye from ryxx where ryid=(select ryid from dbo.OrderList where OrderId=@oederid )),
                                                            getdate(),'正常消费','消费','管理员',null,'02','',(select grye from ryxx where  ryid=(select ryid from dbo.OrderList where OrderId=@oederid )),
                                                            (select btye from ryxx where  ryid=(select ryid from dbo.OrderList where OrderId=@oederid )),null,getdate(),
                                                            @oederid,(select top(1) canteen From lssj where OrderId=@oederid) from lssj where orderid=@oederid and zt='02'	
                                                        end		
                                                set @i=@i+1	
                                                set @fzt='00'
                                                end ";

            int i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sqlstr, sp);
            return i;
        }

        public string GetZt(string Orderid)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@Orderid",Orderid)
            };
            string sql = "select top(1) zt From lssj where Orderid=@Orderid order by xfsj desc";
            return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, sp).ToString();
        }

        //退款完成后将数量返还回去
        public DataSet SelAddsdTime(string oederIds)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@oederIds",oederIds)
            };
            string sql = "select OnSaleId,OrderNum,canteen from OrderDetail where OrderId=@oederIds";
            return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp);
        }

        /// <summary>
        /// 通过编号查询余额
        /// </summary>
        /// <param name="user_phone"></param>
        /// <returns></returns>
        public DataTable Select_CUST_ID01(string oederid)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@oederid",oederid)
            };
                string sql = "select top(1) bankcard,dz,bz from ryxx where 1=1 and rfzt='32' and ryid in (select ryid from dbo.OrderList where OrderId=@oederid) ";
                return SqlHelper.ExecuteDataSet(conn, CommandType.Text, sql, sp).Tables[0];
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错方法Select_CUST_ID01：" + ex.Message);
                return dataTable;
            }
        }

        public string GetOrderList(string oederid)
        {
            try
            {
                SqlParameter[] sp = new SqlParameter[] {
                new SqlParameter("@oederid",oederid)
                };
                string sql = "select TotalPrice From OrderList where OrderId=@oederid";
                return SqlHelper.ExecuteScalar(conn, CommandType.Text, sql, sp).ToString();
            }
            catch (Exception ex)
            {
                ApplicationLog.Write("报错方法GetOrderList：" + ex.Message);
                return "-1";
            }
        }


        //将退款数量返回回去
        public int menulistNum(string OnSaleId, int OrderNum, string canteen)
        {
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@OnSaleId",OnSaleId),
                new SqlParameter("@OrderNum",OrderNum),
                new SqlParameter("@canteen",canteen)
            };
            string sql = "";
            if (canteen == "超市")
            {
                sql = "update MenuList set Inventories=Inventories+CONVERT(int,@OrderNum) where MenuId in (select MenuId from Shelves where OnSaleId=@OnSaleId)";
            }
            else
            {
                sql = "update Shelves set ShelvesNum=ShelvesNum+CONVERT(int,@OrderNum)  where OnSaleId=@OnSaleId";
            }
            int i = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sql, sp);
            return i;
        }

        //退款金额
        public int Refundmode(string OR)
	{
	    SqlParameter[] sp = new SqlParameter[]{
		new SqlParameter("@Orderid",OR)
	    };
	    string sql = "usp_Refundmode";
	    int i = SqlHelper.ExecuteNonQuery(conn,CommandType.StoredProcedure, sql, sp);
	    return i;
	}

	//public string Refund(string OR)
	//{
	//    SqlConnection con = new SqlConnection(conn);
	//    SqlCommand cmd = new SqlCommand("Refund", con);
	//    cmd.CommandType = System.Data.CommandType.StoredProcedure;
	//    cmd.Parameters.AddWithValue("@oederid", "OR");
	//    con.Open();
	//    cmd.ExecuteScalar().ToString();
	//    con.Close();
	//    return "";
	//}
	//public static int addPayTolssj(string openid, double money, string time)
	//{
	//    SqlParameter[] sp = new SqlParameter[]{
	//        new SqlParameter("@openid",openid),
	//        new SqlParameter("@money",money),
	//        new SqlParameter("@xfsj",time)
	//    };
	//    string i = SqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, "WxPayAdd", sp).ToString();
	//    return Int32.Parse(i);
	//}
    }
}
